﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace StoreLocatorAWC
{
    public class DataHelper
    {
        
        public static DataSet GetStoresDS()
        {
            SqlConnection storesConnection = new SqlConnection();
            DataSet storesDataSet = new DataSet();
            SqlDataAdapter storesSqlDataAdapter = new SqlDataAdapter();
            string sqlString;
            sqlString = @"SELECT	A.CustomerID as 'StoreID',
		                            A.Name as 'StoreName',
		                            B.AddressID,
		                            C.AddressLine1,
		                            C.AddressLine2,
		                            C.City,
		                            C.PostalCode,
		                            D.Name as 'State',
		                            E.Name as 'Country',
		                            F.Latitude,
		                            F.Longitude
                        FROM Sales.Store A
                        JOIN Sales.CustomerAddress B ON A.CustomerID = B.CustomerID
                        JOIN Person.Address C ON B.AddressID = C.AddressID
                        JOIN Person.StateProvince D ON C.StateProvinceID = D.StateProvinceID
                        JOIN Person.CountryRegion E ON D.CountryRegionCode = E.CountryRegionCode
                        JOIN Person.GeoCode F ON B.AddressID = F.AddressID
                        WHERE E.Name = 'United States'";

            storesConnection.ConnectionString = @"Data Source = localhost;
                                                   Initial Catalog = AdventureWorks;
                                                   Integrated Security=True";
            storesSqlDataAdapter = new SqlDataAdapter(sqlString, storesConnection);
            storesSqlDataAdapter.Fill(storesDataSet);
            storesConnection.Close();
            return storesDataSet;
        }


    }
}
